package conceptmaniax.hotelapp.managers;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import android.content.Context;
import android.database.Cursor;
import conceptmaniax.hotelapp.config.Constants;
import conceptmaniax.hotelapp.enums.Language;
import conceptmaniax.hotelapp.pojos.Request;
import conceptmaniax.hotelapp.pojos.Service;
import conceptmaniax.hotelapp.util.GeneralUtils;
import conceptmaniax.hotelapp.util.database.DataHelper;

public class RequestManager {
	private static final RequestManager INSTANCE = new RequestManager();
	private Context context;
	private Language language;

	private RequestManager() {

	}

	public static RequestManager getInstance() {
		return INSTANCE;
	}

	public void init(Context context) {
		this.context = context.getApplicationContext();
	}

	public void storeRequests(String notes, String request_time, String insert_time, String menu_id, 
			List<Service> serviceList) {

		final DataHelper dataHelper = new DataHelper(context, Constants.REQUEST_DATABASE_NAME);
		try {
			String requestQuery = "INSERT INTO request (notes, request_time, insert_time, menu_id) "
					+ "VALUES ('" + notes + "', '" + request_time + "', '" + insert_time + "', '" + menu_id + "')";
			dataHelper.openDatabase(true);
			dataHelper.getDataBase().beginTransaction();
			dataHelper.getDataBase().compileStatement(requestQuery).execute();
	
			String lastrowQuery = "SELECT last_insert_rowid()";
			Cursor cursor = dataHelper.getDataBase().rawQuery(lastrowQuery, null);
			cursor.moveToFirst();
			long lastrow = cursor.getLong(0);
			cursor.close();
	
			for (int i = 0; i < serviceList.size(); i++) {
				String serviceQuery = "INSERT INTO request_service (request_id, service_id)"
						+ " VALUES ('" + lastrow + "', '" + serviceList.get(i).getId() + "')";
				dataHelper.getDataBase().compileStatement(serviceQuery).execute();
			}
			
			dataHelper.getDataBase().setTransactionSuccessful();
			
		} finally {
			if (dataHelper != null) {
				dataHelper.getDataBase().endTransaction();
				dataHelper.close();
			}
		}
	}

	public int getRequestCount() throws ParseException {
		int requestCount = 0;
		final DataHelper dataHelper = new DataHelper(context, Constants.REQUEST_DATABASE_NAME);
		String requestQuery = "SELECT request_time FROM request";
		Cursor cursor = dataHelper.getData(requestQuery);
		
		while (cursor.moveToNext()) {
			String dateStr = cursor.getString(0);
			String curStr = new SimpleDateFormat("dd/M/yy, HH:mm", Locale.getDefault()).format(new Date());
			if (dateStr.length() == 0) {
				requestCount++;
			} else {
				Date requestDate = new SimpleDateFormat("dd/M/yy, HH:mm", Locale.getDefault()).parse(dateStr);
				Date currentDate = new SimpleDateFormat("dd/M/yy, HH:mm", Locale.getDefault()).parse(curStr);
				if (currentDate.before(requestDate)) {
					requestCount++;
				}
			}
		}
		cursor.close();
		
		if (dataHelper != null) {
			dataHelper.close();
		}
		
		return requestCount;
	}

	public List<Request> getRequests() {
	    this.language  = GeneralUtils.getLanguage(context);
		List<Request> requestList = new ArrayList<Request>();
		DataHelper dataHelper = new DataHelper(context, Constants.REQUEST_DATABASE_NAME);
		dataHelper.openDatabase(false);
		dataHelper.attachData(Constants.DATABASE_NAME);
		String requestQuery = null;
		switch(this.language) {
		case ENGLISH:
			 requestQuery = "SELECT r._id, r.notes, r.insert_time, "
					 	  + "r.request_time, m.name_en FROM request r INNER JOIN "
						  + "db2.menu m ON m._id = r.menu_id "
						  + "ORDER BY r.insert_time DESC";
				break;
		case GREEK:
			 requestQuery = "SELECT r._id, r.notes, r.insert_time, "
					      + "r.request_time, m.name_gr FROM request r INNER JOIN "
					      + "db2.menu m ON m._id = r.menu_id "
					      + "ORDER BY r.insert_time DESC";
			break;
		}
			
		Cursor requestCursor = dataHelper.getData(requestQuery, true);
		while (requestCursor.moveToNext()) {
			String[] temp = new String[5];
			temp[0] = (requestCursor.getString(0)); // _id
			temp[1] = requestCursor.getString(1); // notes
			temp[2] = requestCursor.getString(2); // insert_time
			temp[3] = requestCursor.getString(3); // request_time
			temp[4] = requestCursor.getString(4); // menu_name
			requestList.add(new Request(temp[0], temp[1], temp[2], temp[3], temp[4]));
		}
		requestCursor.close();
		
		String serviceQuery = null;
		for (int i = 0; i < requestList.size(); i++) {
			serviceQuery = "SELECT s.name "
					+ "FROM request r, request_service rs " + "INNER JOIN "
					+ "db2.service s ON s._id = rs.service_id "
					+ "WHERE r._id = '" + requestList.get(i).getId() + "'"
					+ "AND r._id = rs.request_id ORDER BY s.name ASC";
			Cursor serviceCursor = dataHelper.getData(serviceQuery, true);
			
			while (serviceCursor.moveToNext()) {
				Request request = requestList.get(i);
				request.addService(serviceCursor.getString(0));
			}
			
			serviceCursor.close();
		}
		
		if (dataHelper != null) {
			dataHelper.close();
		}
		
		return requestList;
	}
}